
-- 检查库是否存在
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'School') 
BEGIN
   CREATE DATABASE School COLLATE Chinese_PRC_CI_AS;
END
GO 

USE School
GO


-- ----------------------------
-- Table structure for Courses
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Courses]') AND type IN ('U'))
	DROP TABLE [dbo].[Courses]
GO

CREATE TABLE [dbo].[Courses] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [Name] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [TeacherID] int  NULL
)
GO

ALTER TABLE [dbo].[Courses] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'课程编号',
'SCHEMA', N'dbo',
'TABLE', N'Courses',
'COLUMN', N'ID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'课程名称',
'SCHEMA', N'dbo',
'TABLE', N'Courses',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'教师编号',
'SCHEMA', N'dbo',
'TABLE', N'Courses',
'COLUMN', N'TeacherID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'课程表',
'SCHEMA', N'dbo',
'TABLE', N'Courses'
GO


-- ----------------------------
-- Records of Courses
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Courses] ON
GO

INSERT INTO [dbo].[Courses] ([ID], [Name], [TeacherID]) VALUES (N'1', N'数学', N'2')
GO

INSERT INTO [dbo].[Courses] ([ID], [Name], [TeacherID]) VALUES (N'2', N'语文', N'1')
GO

INSERT INTO [dbo].[Courses] ([ID], [Name], [TeacherID]) VALUES (N'3', N'英语', N'3')
GO

SET IDENTITY_INSERT [dbo].[Courses] OFF
GO


-- ----------------------------
-- Table structure for Enrollments
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Enrollments]') AND type IN ('U'))
	DROP TABLE [dbo].[Enrollments]
GO

CREATE TABLE [dbo].[Enrollments] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [StudentID] int  NOT NULL,
  [CourseID] int  NOT NULL
)
GO

ALTER TABLE [dbo].[Enrollments] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'选课ID',
'SCHEMA', N'dbo',
'TABLE', N'Enrollments',
'COLUMN', N'ID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'学生ID',
'SCHEMA', N'dbo',
'TABLE', N'Enrollments',
'COLUMN', N'StudentID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'课程ID',
'SCHEMA', N'dbo',
'TABLE', N'Enrollments',
'COLUMN', N'CourseID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'选课表',
'SCHEMA', N'dbo',
'TABLE', N'Enrollments'
GO


-- ----------------------------
-- Records of Enrollments
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Enrollments] ON
GO

INSERT INTO [dbo].[Enrollments] ([ID], [StudentID], [CourseID]) VALUES (N'1', N'1', N'1')
GO

INSERT INTO [dbo].[Enrollments] ([ID], [StudentID], [CourseID]) VALUES (N'2', N'1', N'2')
GO

INSERT INTO [dbo].[Enrollments] ([ID], [StudentID], [CourseID]) VALUES (N'3', N'2', N'1')
GO

INSERT INTO [dbo].[Enrollments] ([ID], [StudentID], [CourseID]) VALUES (N'4', N'3', N'3')
GO

SET IDENTITY_INSERT [dbo].[Enrollments] OFF
GO


-- ----------------------------
-- Table structure for Students
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type IN ('U'))
	DROP TABLE [dbo].[Students]
GO

CREATE TABLE [dbo].[Students] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [Name] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Age] int  NOT NULL,
  [Gender] nvarchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[Students] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'学号',
'SCHEMA', N'dbo',
'TABLE', N'Students',
'COLUMN', N'ID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'姓名',
'SCHEMA', N'dbo',
'TABLE', N'Students',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'年龄',
'SCHEMA', N'dbo',
'TABLE', N'Students',
'COLUMN', N'Age'
GO

EXEC sp_addextendedproperty
'MS_Description', N'性别',
'SCHEMA', N'dbo',
'TABLE', N'Students',
'COLUMN', N'Gender'
GO

EXEC sp_addextendedproperty
'MS_Description', N'学生表',
'SCHEMA', N'dbo',
'TABLE', N'Students'
GO


-- ----------------------------
-- Records of Students
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Students] ON
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Gender]) VALUES (N'1', N'张三', N'20', N'男')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Gender]) VALUES (N'2', N'李四', N'21', N'女')
GO

INSERT INTO [dbo].[Students] ([ID], [Name], [Age], [Gender]) VALUES (N'3', N'王五', N'22', N'男')
GO

SET IDENTITY_INSERT [dbo].[Students] OFF
GO


-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Teacher]') AND type IN ('U'))
	DROP TABLE [dbo].[Teacher]
GO

CREATE TABLE [dbo].[Teacher] (
  [ID] int  IDENTITY(1,1) NOT NULL,
  [Name] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[Teacher] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'教师ID',
'SCHEMA', N'dbo',
'TABLE', N'Teacher',
'COLUMN', N'ID'
GO

EXEC sp_addextendedproperty
'MS_Description', N'教师姓名',
'SCHEMA', N'dbo',
'TABLE', N'Teacher',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'教师表',
'SCHEMA', N'dbo',
'TABLE', N'Teacher'
GO


-- ----------------------------
-- Records of Teacher
-- ----------------------------
SET IDENTITY_INSERT [dbo].[Teacher] ON
GO

INSERT INTO [dbo].[Teacher] ([ID], [Name]) VALUES (N'1', N'李老师')
GO

INSERT INTO [dbo].[Teacher] ([ID], [Name]) VALUES (N'2', N'张老师')
GO

INSERT INTO [dbo].[Teacher] ([ID], [Name]) VALUES (N'3', N'王老师')
GO

SET IDENTITY_INSERT [dbo].[Teacher] OFF
GO


-- ----------------------------
-- Auto increment value for Courses
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[Courses]', RESEED, 3)
GO


-- ----------------------------
-- Primary Key structure for table Courses
-- ----------------------------
ALTER TABLE [dbo].[Courses] ADD CONSTRAINT [PK__Courses__C92D7187DFD60CA0] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for Enrollments
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[Enrollments]', RESEED, 4)
GO


-- ----------------------------
-- Primary Key structure for table Enrollments
-- ----------------------------
ALTER TABLE [dbo].[Enrollments] ADD CONSTRAINT [PK__Enrollme__7F6877FBF8D05A53] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for Students
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[Students]', RESEED, 3)
GO


-- ----------------------------
-- Primary Key structure for table Students
-- ----------------------------
ALTER TABLE [dbo].[Students] ADD CONSTRAINT [PK__Students__32C52A790BE29C74] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Auto increment value for Teacher
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[Teacher]', RESEED, 3)
GO


-- ----------------------------
-- Primary Key structure for table Teacher
-- ----------------------------
ALTER TABLE [dbo].[Teacher] ADD CONSTRAINT [PK__Teacher__3214EC27CC2A293F] PRIMARY KEY CLUSTERED ([ID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table Courses
-- ----------------------------
ALTER TABLE [dbo].[Courses] ADD CONSTRAINT [FK_Cource_Teacher] FOREIGN KEY ([TeacherID]) REFERENCES [dbo].[Teacher] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table Enrollments
-- ----------------------------
ALTER TABLE [dbo].[Enrollments] ADD CONSTRAINT [FK_Enrollments_Students] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[Enrollments] ADD CONSTRAINT [FK_Enrollments_Courses] FOREIGN KEY ([CourseID]) REFERENCES [dbo].[Courses] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO